package cn.com.headfree.generalquery.dao.impl;

import cn.com.headfree.generalquery.vo.GeneralManageQueryVo;
import cn.com.headfree.generalquery.dao.GeneralManageQueryDao;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.common.dto.Page;
import com.gta.edu.sdk.util.StringUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository("generalManageQueryDao")
public class GeneralManageQueryDaoImpl extends BaseDaoImpl<GeneralManageQueryVo> implements GeneralManageQueryDao {


    @Override
    public void findPersonGeneralInfoListByCondition(Page<GeneralManageQueryVo> page, GeneralManageQueryVo vo) {
        String sql = "select * from ( select t.id,t.erp_no,t.name,TIMESTAMPDIFF(YEAR, t.birth_date, CURDATE()) as age,YEAR(CURDATE())-YEAR(t.job_date) + 1 as employlength,t.job_date, ";
        StringBuffer sb = new StringBuffer();
        ArrayList<Object> arrayList = new ArrayList<Object>();
        sb.append(sql);
        sb.append(" (select e.educ_name from t_educate_info e where e.erp_no = t.erp_no and e.is_valid = 0 and e.del_sign = 0 ");
        if(vo.getEducName() != null && vo.getEducName().size() > 0){
            sb.append(" and FIND_IN_SET(e.educ_name,?) ");
            arrayList.add(StringUtils.join(vo.getEducName(),","));
        }
        sb.append(" ORDER BY e.educ_name asc LIMIT 1) as education_name, ");
        sb.append(" (select s.qualifi_level from t_skill_level_info s where s.erp_no = t.erp_no and s.is_valid = 0 and s.del_sign = 0 ");
        if(vo.getQualifiLevel() != null && vo.getQualifiLevel().size() > 0){
            sb.append(" and FIND_IN_SET(s.qualifi_level,?) ");
            arrayList.add(StringUtils.join(vo.getQualifiLevel(),","));
        }
        sb.append(" ORDER BY s.qualifi_level asc LIMIT 1) as qualifilevelname, ");
        sb.append(" (select r.emp_title from t_expert_title_info r where r.erp_no = t.erp_no and r.is_valid = 0 and r.del_sign = 0 ORDER BY r.acqu_time desc LIMIT 1) as emp_title_name, ");
        sb.append(" (select p.subj_level from t_pro_tec_info p where p.erp_no = t.erp_no and p.is_valid = 0 and p.del_sign = 0 ");
        if(vo.getSubjLevel() != null && vo.getSubjLevel().size() > 0){
            sb.append(" and FIND_IN_SET(p.subj_level,?) ");
            arrayList.add(StringUtils.join(vo.getSubjLevel(),","));
        }
        sb.append(" ORDER BY p.subj_level asc LIMIT 1) as subjlevelname ");
        sb.append(" from t_emp_base_info t ")
          .append(" LEFT JOIN t_dept_emp_rela d on t.erp_no = d.erp_no ")
          .append(" LEFT JOIN t_org_dept o on o.id = d.dept_id ")
          .append(" where 1=1 ");
        if(!StringUtil.isNullOrEmpty(vo.getName())){
            sb.append(" and t.name like concat('%',?,'%') ESCAPE '/' ");
            arrayList.add(vo.getName());
        }
        if(!StringUtil.isNullOrEmpty(vo.getDeptId())){
            sb.append(" and o.path like concat('%',?,'%') ESCAPE '/' ");
            arrayList.add(vo.getDeptId());
        }
        if(!StringUtil.isNullOrEmpty(vo.getOrgId())){
            sb.append(" and t.org_id = ? ");
            arrayList.add(vo.getOrgId());
        }
        sb.append(" ) k where 1=1 ");
        if(vo.getEducName() != null && vo.getEducName().size() > 0){
            sb.append(" and FIND_IN_SET(k.education_name,?) ");
            arrayList.add(StringUtils.join(vo.getEducName(),","));
        }
        if(vo.getQualifiLevel() != null && vo.getQualifiLevel().size() > 0){
            sb.append(" and FIND_IN_SET(k.qualifilevelname,?) ");
            arrayList.add(StringUtils.join(vo.getQualifiLevel(),","));
        }
        if(vo.getIsExpert() != null && vo.getIsExpert() == 0){//是专家人才
            sb.append(" and k.emp_title_name is not null ");
        }else if(vo.getIsExpert() != null && vo.getIsExpert() == 1){//不是专家人才
            sb.append(" and k.emp_title_name is null ");
        }
        if(vo.getSubjLevel() != null && vo.getSubjLevel().size() > 0){
            sb.append(" and FIND_IN_SET(k.subjlevelname,?) ");
            arrayList.add(StringUtils.join(vo.getSubjLevel(),","));
        }
        if(vo.getAgeStart() != null && vo.getAgeStart() != 0){//年龄范围开始值
            sb.append(" and k.age >= ?");
            arrayList.add(vo.getAgeStart());
        }
        if(vo.getAgeEnd() != null && vo.getAgeEnd() != 0){//年龄范围结束值
            sb.append(" and k.age <= ?");
            arrayList.add(vo.getAgeEnd());
        }
        if(vo.getEmployLengthStart() != null && vo.getEmployLengthStart() != 0){//工龄范围开始值
            sb.append(" and k.employlength >= ?");
            arrayList.add(vo.getEmployLengthStart());
        }
        if(vo.getEmployLengthEnd() != null && vo.getEmployLengthEnd() != 0){//工龄范围结束值
            sb.append(" and k.employlength <= ?");
            arrayList.add(vo.getEmployLengthEnd());
        }
        if(!StringUtil.isNullOrEmpty(vo.getSortField())){
            if(!StringUtil.isNullOrEmpty(vo.getSort())){
                sb.append(" order by "+vo.getSortField()+" "+vo.getSort()+" ");
            }
        }else{
            sb.append(" order by k.erp_no asc ");
        }
        this.findPageBySql(GeneralManageQueryVo.class, sb.toString(), page, arrayList.toArray());
    }

    @Override
    public List<GeneralManageQueryVo> exportPersonGeneralInfoListByCondition(GeneralManageQueryVo vo) {
        String sql = "select * from ( select t.id,t.erp_no,t.name,TIMESTAMPDIFF(YEAR, t.birth_date, CURDATE()) as age,YEAR(CURDATE())-YEAR(t.job_date) + 1 as employlength,t.job_date, ";
        StringBuffer sb = new StringBuffer();
        ArrayList<Object> arrayList = new ArrayList<Object>();
        sb.append(sql);
        sb.append(" (select e.educ_name from t_educate_info e where e.erp_no = t.erp_no and e.is_valid = 0 and e.del_sign = 0 ");
        if(vo.getEducName() != null && vo.getEducName().size() > 0){
            sb.append(" and FIND_IN_SET(e.educ_name,?) ");
            arrayList.add(StringUtils.join(vo.getEducName(),","));
        }
        sb.append(" ORDER BY e.educ_name asc LIMIT 1) as education_name, ");
        sb.append(" (select s.qualifi_level from t_skill_level_info s where s.erp_no = t.erp_no and s.is_valid = 0 and s.del_sign = 0 ");
        if(vo.getQualifiLevel() != null && vo.getQualifiLevel().size() > 0){
            sb.append(" and FIND_IN_SET(s.qualifi_level,?) ");
            arrayList.add(StringUtils.join(vo.getQualifiLevel(),","));
        }
        sb.append(" ORDER BY s.qualifi_level asc LIMIT 1) as qualifilevelname, ");
        sb.append(" (select r.emp_title from t_expert_title_info r where r.erp_no = t.erp_no and r.is_valid = 0 and r.del_sign = 0 ORDER BY r.acqu_time desc LIMIT 1) as emp_title_name, ");
        sb.append(" (select p.subj_level from t_pro_tec_info p where p.erp_no = t.erp_no and p.is_valid = 0 and p.del_sign = 0 ");
        if(vo.getSubjLevel() != null && vo.getSubjLevel().size() > 0){
            sb.append(" and FIND_IN_SET(p.subj_level,?) ");
            arrayList.add(StringUtils.join(vo.getSubjLevel(),","));
        }
        sb.append(" ORDER BY p.subj_level asc LIMIT 1) as subjlevelname ");
        sb.append(" from t_emp_base_info t ")
                .append(" LEFT JOIN t_dept_emp_rela d on t.erp_no = d.erp_no ")
                .append(" LEFT JOIN t_org_dept o on o.id = d.dept_id ")
                .append(" where 1=1 ");
        if(!StringUtil.isNullOrEmpty(vo.getName())){
            sb.append(" and t.name like concat('%',?,'%') ESCAPE '/' ");
            arrayList.add(vo.getName());
        }
        if(!StringUtil.isNullOrEmpty(vo.getDeptId())){
            sb.append(" and o.path like concat('%',?,'%') ESCAPE '/' ");
            arrayList.add(vo.getDeptId());
        }
        if(!StringUtil.isNullOrEmpty(vo.getOrgId())){
            sb.append(" and t.org_id = ? ");
            arrayList.add(vo.getOrgId());
        }
        sb.append(" ) k where 1=1 ");
        if(vo.getEducName() != null && vo.getEducName().size() > 0){
            sb.append(" and FIND_IN_SET(k.education_name,?) ");
            arrayList.add(StringUtils.join(vo.getEducName(),","));
        }
        if(vo.getQualifiLevel() != null && vo.getQualifiLevel().size() > 0){
            sb.append(" and FIND_IN_SET(k.qualifilevelname,?) ");
            arrayList.add(StringUtils.join(vo.getQualifiLevel(),","));
        }
        if(vo.getIsExpert() != null && vo.getIsExpert() == 0){//是专家人才
            sb.append(" and k.emp_title_name is not null ");
        }else if(vo.getIsExpert() != null && vo.getIsExpert() == 1){//不是专家人才
            sb.append(" and k.emp_title_name is null ");
        }
        if(vo.getSubjLevel() != null && vo.getSubjLevel().size() > 0){
            sb.append(" and FIND_IN_SET(k.subjlevelname,?) ");
            arrayList.add(StringUtils.join(vo.getSubjLevel(),","));
        }
        if(vo.getAgeStart() != null && vo.getAgeStart() != 0){//年龄范围开始值
            sb.append(" and k.age >= ?");
            arrayList.add(vo.getAgeStart());
        }
        if(vo.getAgeEnd() != null && vo.getAgeEnd() != 0){//年龄范围结束值
            sb.append(" and k.age <= ?");
            arrayList.add(vo.getAgeEnd());
        }
        if(vo.getEmployLengthStart() != null && vo.getEmployLengthStart() != 0){//工龄范围开始值
            sb.append(" and k.employlength >= ?");
            arrayList.add(vo.getEmployLengthStart());
        }
        if(vo.getEmployLengthEnd() != null && vo.getEmployLengthEnd() != 0){//工龄范围结束值
            sb.append(" and k.employlength <= ?");
            arrayList.add(vo.getEmployLengthEnd());
        }
        if(vo.getIds() != null && vo.getIds().size() > 0){
            sb.append(" and FIND_IN_SET(k.id,?) ");
            arrayList.add(StringUtils.join(vo.getIds(),","));
        }
        if(!StringUtil.isNullOrEmpty(vo.getSortField())){
            if(!StringUtil.isNullOrEmpty(vo.getSort())){
                sb.append(" order by "+vo.getSortField()+" "+vo.getSort()+" ");
            }
        }else{
            sb.append(" order by k.erp_no asc ");
        }
        return this.findAllBySQL(GeneralManageQueryVo.class, sb.toString(), null, false, arrayList.toArray());
    }
}
